Generated code - Entity collection and Typed List/Typed View paging, Adapter
Preface
Paging is the way to browse through a list of objects or rows of data one page at a time. This can be handy when you have thousands of rows / objects matching
search criteria but you want to enlist only a small number at once. With the paging functionality built into the DataAccessAdapter class, you can tell
the generated code which page to retrieve for typed lists, typed views or entity collections, instead of getting all the results at once.
This section describes the various options you have.
 Note:
|
|
On SqlServer 2000, paging is implemented using temp tables. This is
done as it gives reasonable performance in all situations (small/large resultsets). Paging using ROWCOUNT tricks is not possible due to the
fact that this kind of paging is pretty limited when it comes to compound primary keys. On SqlServer 2005+, paging is done through a CTE query. Please
refer to Generated code - Application configuration through .config files and
Generated code - Database specific features how to set the SqlServer DQE into SqlServer 2005 compatibility mode
so it will use a CTE based query instead of a temp table based query.
|
Paging through an entity collection
Paging through an entity collection is implemented in an overload of DataAccessAdapter.FetchEntityCollection(). The particular overload accepts the page size,
which is the number of objects to retrieve by the fetch action, and the page number to retrieve. If you for example pass 10 for the page size and 4 for
the page number, you'll get record number 31-40, the first record is 1, the first page is also numbered 1. Paging is disabled if you pass 0 for the page
number or 0 or 1 for the page size.
Get the total number of objects
To effectively use paging, it is key to know how many pages there are. For example, you want to show a list of page numbers the user can choose from, like
Google uses. You can retrieve the number of objects matching your filter by using the DataAccessAdapter's
GetDbCount() method.
The method below uses an Aggregate function. Read more about aggregate functions and expressions in
the section
Field expressions and aggregates. The example retrieves the number of different order
objects of customers from France.
// C#
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(CustomerFields.Country == "France");
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);
DataAccessAdapter adapter = new DataAccessAdapter();
int amount = (int)adapter.GetDbCount(new OrderEntityFactory().CreateFields(), filter, null, false);
' VB.NET
Dim filter As IRelationPredicateBucket = New RelationPredicateBucket()
filter.PredicateExpression.Add(CustomerFields.Country = "France")
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId)
Dim adapter As New DataAccessAdapter()
Dim amount As Integer = CInt(adapter.GetDbCount(new OrderEntityFactory().CreateFields(), filter, Nothing, False))
The value in
amount can now be used to calculate the total number of pages when the page size is given: total number of pages = (total number of
objects / pagesize) + n, where n is either 0 (total number of objects modulo pagesize is 0) or 1 (total number of objects modulo pagesize > 0).
Below is the code to retrieve page 4, with a pagesize of 10 objects. We re-use the filter objects used in the GetScalar() call:
// C#
EntityCollection orders = new EntityCollection(new OrderEntityFactory());
adapter.FetchEntityCollection(orders, filter, 0, null, 4, 10);
' VB.NET
Dim orders As New EntityCollection(New OrderEntityFactory())
adapter.FetchEntityCollection(orders, filter, 0, Nothing, 4, 10)
After this call, orders contains 10 objects, which formed the 4th page in the result set matching the filter defined. No sorting is applied here, but if you
specify a sort expression, the sorting is performed prior to the paging logic.
Paging through a TypedList or TypedView
The paging functionality is also available for typed list and typed view classes, through an overload of the FetchTypedList() method for typed lists and
an overload of FetchTypedView for typed views. For typed lists and typed views, the same definitions are valid as for collections: page numbers start at
1, the first record is numbered 1 and paging is disabled if you pass in a page number of 0 or you pass in a page size of 0 or 1.
Get the total number of rows
Getting the total number of rows for a typed list, is a bit different than it is for a collection. Instead of creating a new RelationPredicateBucket object, you
use the RelationPredicateBucket returned from the typed list's method GetRelationInfo(). To that, you add your predicates. For typed views, it works the same
as enlisted above for entities. Below is the code to get the number of rows in a typed list with order-customer rows.
// C#
OrderCustomerTypedList orderCustomer = new OrderCustomerTypedList();
IEntityFields2 fields = orderCustomer.GetFieldsInfo();
IRelationPredicateBucket filter = orderCustomer.GetRelationInfo();
filter.PredicateExpression.Add(CustomerFields.Country == "France");
DataAccessAdapter adapter = new DataAccessAdapter();
int amount = (int)adapter.GetDbCount(fields, filter, null, false);
' VB.NET
Dim orderCustomer As New OrderCustomerTypedList()
Dim fields As IEntityFields2 = orderCustomer.GetFieldsInfo()
Dim filter As IRelationPredicateBucket = orderCustomer.GetRelationInfo()
filter.PredicateExpression.Add(New FieldCompareValuePredicate( _
CustomerFields.Country, Nothing, ComparisonOperator.Equal, "France"))
Dim adapter As New DataAccessAdapter()
Dim amount As Integer = CInt(adapter.GetDbCount(fields, filter, Nothing, False))
Fetching a given page in the typed list or typed view is then boiling down to using the FetchTypedList() overload which accepts the two paging parameters
for fetching a typed list' page or calling the proper FetchTypedView method for fetching the required page for a typed view. Be sure to
clear the typed list/typed view object before calling the fetch methods again to fetch another page.
